﻿Imports System.Data
Imports System.Data.SqlClient
Public Class DBUtils
    Private strConnection As String ' = My.Settings.TanNamChinhConnectionString2

    Private sqlConn As SqlConnection
    Sub setConnectionString(ByVal value As String)
        strConnection = value
    End Sub
    Public Sub TestConnection()
        Try
            sqlConn = New SqlConnection(strConnection)
            sqlConn.Open()
            If sqlConn.State = ConnectionState.Open Then
                MessageBox.Show("Connection Succeed!")
            End If
        Catch ex As Exception
            MessageBox.Show("Connection Fail!")
        End Try


    End Sub
    Function ExecuteQuery(ByVal strSQL As String) As Integer
        Try
            Dim cmd As New SqlCommand

            With cmd
                .Connection = New SqlConnection(strConnection)
                .Connection.Open()
                .CommandText = strSQL
                '.CommandType = CommandType.Text
                Return .ExecuteNonQuery()
                .Connection.Close()
            End With
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Sub UpdateBiayaPenawaran(ByVal offerid As Integer, ByVal tipe As Integer, ByVal value As Double)
        Dim sql As String = String.Format("update tof_others set value={0} where offer_id={1} and expense_id={2}", _
                                        CStr(value), CStr(offerid), CStr(tipe))
        ExecuteQuery(sql)
    End Sub
    Sub UpdatePrivileges(ByVal pid As Integer, ByVal grant As Boolean)
        Dim sql As String
        If grant Then
            sql = String.Format("udpate app_privileges set granted=1 where id={0}", pid)
        Else
            sql = String.Format("udpate app_privileges set granted=0 where id={0}", pid)

        End If

        ExecuteQuery(sql)
    End Sub
    Sub BackupDatabase(ByVal filepath As String)
        Dim sql As String = _
            String.Format("BACKUP DATABASE [freights] TO DISK = N'{0}' WITH NOFORMAT, NOINIT, NAME=N'freights-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10", filepath)
        ExecuteQuery(sql)

    End Sub
    Sub RestoreDatabase(ByVal filepath As String)
        Dim sql As String = _
            String.Format("RESTORE DATABASE [freights] FROM DISK = N'{0}' WITH FILE = 1,  NOUNLOAD, STATS = 10", filepath)
        ExecuteQuery(sql)
    End Sub
End Class
